﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Xml.XPath;

namespace uForum.Library {
    /// <summary>
    /// This class contains raw xml from the database server, so extra coloumns addded etc, will be included in these feeds.
    /// </summary>
    public class RawXml {
        
        public static XPathNodeIterator TopicsWithAuthor(int memberId) {
            return Businesslogic.Data.GetDataSet("SELECT * FROM forumTopics where memberid = " + memberId.ToString(), "topic");
        }

        public static XPathNodeIterator Topic(int topicId) {
            return Businesslogic.Data.GetDataSet("SELECT * FROM forumTopics where id = " + topicId.ToString(), "topic");
        }

        public static XPathNodeIterator Forum(int forumId) {
            return Businesslogic.Data.GetDataSet("SELECT * FROM forumForums where id = " + forumId.ToString(), "forum");
        }

        public static XPathNodeIterator Topics(int forumId, int maxItems, int page) {

            // Check for paging
            int pageSize = maxItems;
            int pageStart = page;

            int pageEnd = ((page + 1) * pageSize);

            if (pageStart > 0) {
                pageStart = (page * pageSize) + 1;
            }
            

            string sql = @"WITH Topics  AS
                        (
                            SELECT	id, parentId, memberId, title, body, created, updated, locked, latestReplyAuthor, replies,  urlname, 
		                            ROW_NUMBER() OVER (ORDER BY updated DESC) AS RowNumber
                            FROM	dbo.forumTopics
                            WHERE parentId = " + forumId.ToString() + @"
                        )
                        SELECT	id, parentId, RowNumber, memberId, title, body, created, updated, locked, latestReplyAuthor, replies,  urlname
                        FROM	Topics
                        WHERE	RowNumber BETWEEN " + pageStart.ToString() + " AND " + pageEnd.ToString() + @"  
                        ORDER BY RowNumber ASC;";


            return Businesslogic.Data.GetDataSet(sql, "topic");
        }

        public static XPathNodeIterator CommentsByDate(int topicId, int maxItems, int page, string order) {
            // Check for paging
            int pageSize = maxItems;
            int pageStart = page;

            int pageEnd = ((page + 1) * pageSize);

            if (pageStart > 0) {
                pageStart = (page * pageSize) + 1;
            }
            
                        
            string sql = @"WITH Comments  AS
                            (
	                            SELECT	id, topicId, memberId, body, created,  position,
			                            ROW_NUMBER() OVER (ORDER BY created " + order  + @") AS RowNumber
	                            FROM	dbo.forumComments
	                            WHERE topicId = " + topicId.ToString() + @"
                            )
                            SELECT	id, topicId, memberId, body, created, position, RowNumber
                            FROM	Comments
                            WHERE	RowNumber BETWEEN " + pageStart.ToString() + " AND " + pageEnd.ToString() + @" 
                            ORDER BY RowNumber ASC;";
            
            return Businesslogic.Data.GetDataSet(sql, "comment");
        }

        public static XPathNodeIterator CommentsByScore(int topicId, int maxItems, int page, string order) {

            // Check for paging
            int pageSize = maxItems;
            int pageStart = page;

            int pageEnd = ((page + 1) * pageSize);

            if (pageStart > 0) {
                pageStart = (page * pageSize) + 1;
            }


            string sql = @"WITH Comments  AS
                            (
	                            SELECT	id, topicId, memberId, body, created, position,
			                            ROW_NUMBER() OVER (ORDER BY score " + order + @") AS RowNumber
	                            FROM	dbo.forumComments
	                            WHERE topicId = " + topicId.ToString() + @"
                            )
                            SELECT	id, topicId, memberId, body, created, position, RowNumber
                            FROM	Comments
                            WHERE	RowNumber BETWEEN " + pageStart.ToString() + " AND " + pageEnd.ToString() + @" 
                            ORDER BY RowNumber ASC;";

            return Businesslogic.Data.GetDataSet(sql, "comment");
        }

        public static XPathNodeIterator AllForums() {
            return Businesslogic.Data.GetDataSet("SELECT * FROM forumForums", "forum");
        }

        public static XPathNodeIterator Forums(int parentId) {
            return Businesslogic.Data.GetDataSet("SELECT * FROM forumForums where parentId = " + parentId.ToString(), "forum");
        }


        public static XPathNodeIterator LatestTopicsSinceDate(int maxItems, int page, DateTime sinceDate) {

            // Check for paging
            int pageSize = maxItems;
            int pageStart = page;

            int pageEnd = ((page + 1) * pageSize);

            if (pageStart > 0) {
                pageStart = (page * pageSize) + 1;
            }


            string sql = @"WITH Topics  AS
                        (
                            SELECT	id, parentId, memberId, title, body, created, updated, locked, latestReplyAuthor, replies,  urlname, 
		                            ROW_NUMBER() OVER (ORDER BY updated DESC) AS RowNumber
                            FROM	dbo.forumTopics where updated > " + sinceDate.ToShortDateString() + @"
                        )
                        SELECT	id, parentId, RowNumber, memberId, title, body, created, updated, locked, latestReplyAuthor, replies,  urlname
                        FROM	Topics
                        WHERE	RowNumber BETWEEN " + pageStart.ToString() + " AND " + pageEnd.ToString() + @"  
                        ORDER BY RowNumber ASC;";


            return Businesslogic.Data.GetDataSet(sql, "topic");
        }


        public static XPathNodeIterator LatestTopics(int maxItems, int page) {
            return LatestTopicsSinceDate(maxItems, page, DateTime.MinValue);
        }
    }
}
